{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "source": [
    "## PostgreSQL連携"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# http://qiita.com/hik0107/items/de5785f680096df93efa\n",
    "# グラフ化に必要なものの準備\n",
    "import matplotlib\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "# Jupyter上に図を表示するためのおまじない\n",
    "%matplotlib inline\n",
    "\n",
    "# データの扱いに必要なライブラリ\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import datetime as dt\n",
    "\n",
    "# チャートがきれいになるおまじない\n",
    "plt.style.use('ggplot') \n",
    "\n",
    "# PostgreSQLを扱うためのライブラリ\n",
    "import pandas.io.sql as psql\n",
    "import psycopg2 as pg\n",
    "\n",
    "# 環境変数を使うためのライブラリ\n",
    "import os\n",
    "\n",
    "# yamlを扱うためのライブラリ\n",
    "import yaml"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "      <th>age</th>\n",
       "      <th>address</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Taro</td>\n",
       "      <td>21</td>\n",
       "      <td>Tokyo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Taro</td>\n",
       "      <td>21</td>\n",
       "      <td>Tokyo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Jiro</td>\n",
       "      <td>20</td>\n",
       "      <td>Osaka</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Saburo</td>\n",
       "      <td>19</td>\n",
       "      <td>Nagoya</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id    name  age address\n",
       "0   1    Taro   21   Tokyo\n",
       "1   2    Taro   21   Tokyo\n",
       "2   3    Jiro   20   Osaka\n",
       "3   4  Saburo   19  Nagoya"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### pandasとPostgreSQL連携テスト\n",
    "with pg.connect(database='jsd_development',\n",
    "                user='jsd',\n",
    "                password='pass',\n",
    "                host='10.136.3.189',\n",
    "                port=5432) as conn:\n",
    "  sql = 'SELECT * FROM people'\n",
    "  df = psql.read_sql(sql, conn)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "/root/database.yml\n",
      "development:\n",
      "  database: jsd_development\n",
      "  username: jsd\n",
      "  password: pass\n",
      "  port: 5432\n",
      "  host: 10.136.3.189\n",
      "\n",
      "\n"
     ]
    }
   ],
   "source": [
    "### database.ymlの中身確認\n",
    "yml_path = \"{0}/{1}\".format(os.environ.get(\"HOME\"), \"database.yml\")\n",
    "print(yml_path)\n",
    "\n",
    "f = open(yml_path, \"r\")\n",
    "for line in f:\n",
    "    print(line, end=\"\")\n",
    "print(\"\\n\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>development</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>database</th>\n",
       "      <td>jsd_development</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>host</th>\n",
       "      <td>10.136.3.189</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>password</th>\n",
       "      <td>pass</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>port</th>\n",
       "      <td>5432</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>username</th>\n",
       "      <td>jsd</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              development\n",
       "database  jsd_development\n",
       "host         10.136.3.189\n",
       "password             pass\n",
       "port                 5432\n",
       "username              jsd"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### ymlの内容を読み込んでpandas連携\n",
    "yml_path = \"{0}/{1}\".format(os.environ.get(\"HOME\"), \"database.yml\")\n",
    "f = open(yml_path, \"r\")\n",
    "data = yaml.load(f)\n",
    "\n",
    "pd.DataFrame.from_dict(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "      <th>age</th>\n",
       "      <th>address</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Taro</td>\n",
       "      <td>21</td>\n",
       "      <td>Tokyo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Taro</td>\n",
       "      <td>21</td>\n",
       "      <td>Tokyo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Jiro</td>\n",
       "      <td>20</td>\n",
       "      <td>Osaka</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Saburo</td>\n",
       "      <td>19</td>\n",
       "      <td>Nagoya</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id    name  age address\n",
       "0   1    Taro   21   Tokyo\n",
       "1   2    Taro   21   Tokyo\n",
       "2   3    Jiro   20   Osaka\n",
       "3   4  Saburo   19  Nagoya"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### ymlの内容を使ってPostgresに接続\n",
    "pg_database=data['development']['database']\n",
    "pg_user=data['development']['username']\n",
    "pg_password=data['development']['password']\n",
    "pg_host=data['development']['host']\n",
    "pg_port=data['development']['port']\n",
    "\n",
    "### pandasとPostgreSQL連携テスト\n",
    "with pg.connect(database=pg_database,\n",
    "                user=pg_user,\n",
    "                password=pg_password,\n",
    "                host=pg_host,\n",
    "                port=pg_port) as conn:\n",
    "  sql = 'SELECT * FROM people'\n",
    "  df = psql.read_sql(sql, conn)\n",
    "df"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
